﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Intelsi.Entity;
using System.Data.SqlClient;

namespace Intelsi.DataAcces
{
    public class UbigeoDAO : MasterDataAccess, IUbigeoDAO
    {

        public List<ubigeo> GetAllFromUbigeo()
        {

            //var query = Context.Database.SqlQuery<ubigeo>("DepaUbigeo").ToList();

            //return query;


            var query = from p in Context.ubigeos
                       group p by p.departamento into uniqedepartamento
                        select uniqedepartamento.FirstOrDefault();

            return query.ToList();
        }

        public List<ubigeo> GetAllFromUbigeoByCriterio(string criterio)
        {

            


                var query =( from u in Context.ubigeos
                            where (u.distrito.ToUpper().StartsWith(criterio.ToUpper()))
                           group u by u.distrito into uniqueIds
                           select uniqueIds.FirstOrDefault());

            
                return query.ToList();
            }




        public List<ubigeo> GetAllFromUbigeoByDepartamento(string Departamento, string criterio)
        {
           var query = from u in Context.ubigeos
                           where u.departamento == Departamento
                           where (u.provincia.ToUpper().StartsWith(criterio.ToUpper()))
                           group u by u.provincia into uniqueIds
                           select uniqueIds.FirstOrDefault();

           return query.ToList();
        }


        public List<ubigeo> sinServicio(string criterio)
        {
            var query = from u in Context.ubigeos
                        where u.departamento == criterio
                        group u by u.provincia into uniqueIds
                        select uniqueIds.FirstOrDefault();

            return query.ToList();
        }


        public List<ubigeo> sinServicio2(string departamento, string provincia)
        {
            var query = from u in Context.ubigeos
                        where u.departamento == departamento
                        where u.provincia == provincia
                        group u by u.distrito into uniqueIds
                        select uniqueIds.FirstOrDefault();

            return query.ToList();
        }
    }

     
    }
